package core;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.PreparedStatement;

import java.util.ArrayList;

import java.util.Iterator;
import java.util.List;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;

import dataBase.DbConnection;

import xml.MyDom4j;

public class DataStore {

	private static String GetBeginningString(String tbName) {
		return "INSERT INTO" + " " + tbName;
	}

	private static String GetTableString(String tbName) {
		String InsertTbString = GetBeginningString(tbName);
		return InsertTbString;
	}

	private static String GetFieldString(List<String> nameList,
			List<String> valueList, List<String> columnNameList,
			List<Boolean> binaryFilterList) {

		if (nameList.size() != valueList.size()) {
			System.out.println("Columns Values Number Match failed");
			return null;
		}
		if (nameList.size() == 0) {
			return "";
		}
		StringBuffer fulfilledString = new StringBuffer();

		// String fulfilledString = null;
		int begin = 0;

		for (int i = begin; i < nameList.size(); i++) {
			if (i == begin) {
				// fulfilledString = " " + "(" + nameList.get(i);
				fulfilledString.append(" " + "(" + nameList.get(i));
			} else {
				// fulfilledString += "," + nameList.get(i);

				fulfilledString.append("," + nameList.get(i));
			}
		}
		fulfilledString.append(")" + " " + "VALUES");
		// fulfilledString += ")" + " " + "VALUES";

		for (int i = begin; i < valueList.size(); i++) {
			Boolean qMark = binaryFilterList.get(columnNameList
					.indexOf(nameList.get(i)));
			if (i == begin) {
				if (!qMark) {
					// fulfilledString += " " + "(" + "'" + valueList.get(i) +
					// "'";
					fulfilledString.append(" " + "(" + "'" + valueList.get(i)
							+ "'");
				} else {
					// fulfilledString += " " + "(" + "?";
					fulfilledString.append(" " + "(" + "?");
				}
			} else {
				if (!qMark) {
					// fulfilledString += "," + "'" + valueList.get(i) + "'";
					fulfilledString.append("," + "'" + valueList.get(i) + "'");
				} else {
					// fulfilledString += "," + "?";
					fulfilledString.append("," + "?");
				}
			}
		}
		// fulfilledString += ")" + "";
		fulfilledString.append(")" + "");
		return fulfilledString.toString();
	}

	private static List<String> GetColumnNameList(Element tbStruct) {
		List<String> columnNameList = new ArrayList<String>();
		@SuppressWarnings("unchecked")
		Iterator<Element> fIt = tbStruct.elementIterator("FIELD");
		while (fIt.hasNext()) {
			columnNameList.add(fIt.next().elementText("COLUMN_NAME"));
		}
		return columnNameList;
	}

	private static List<String> GetColumnTypeList(Element tbStruct) {
		List<String> columnTypeList = new ArrayList<String>();
		@SuppressWarnings("unchecked")
		Iterator<Element> fIt = tbStruct.elementIterator("FIELD");
		while (fIt.hasNext()) {
			columnTypeList.add(fIt.next().elementText("TYPE_NAME"));
		}
		return columnTypeList;
	}

	private static List<String> GetRawTypeList(List<String> columnTypeList,
			Element dataTypeElement) {
		List<String> rawTypeList = new ArrayList<String>();
		for (int i = 0; i < columnTypeList.size(); i++) {
			rawTypeList.add(dataTypeElement.elementText(columnTypeList.get(i)));
		}
		return rawTypeList;
	}

	private static List<Boolean> BinaryFilter(List<String> colTypeList,
			Element dataTypeElement) throws DocumentException, Exception {
		// System.out.println(dataTypeElement.asXML());
		List<Boolean> booleanList = new ArrayList<Boolean>();
		for (int i = 0; i < colTypeList.size(); i++) {
			String text = dataTypeElement.elementText(colTypeList.get(i));
			// System.out.println(text);
			if (text.equalsIgnoreCase("Base64Binary")) {
				booleanList.add(true);
			} else {
				booleanList.add(false);
			}
		}
		return booleanList;
	}

	private static List<String> GetTableNameList(Element dataRoot) {
		List<String> tbList = new ArrayList<String>();
		@SuppressWarnings("unchecked")
		Iterator<Element> tbIt = dataRoot.elementIterator("TABLE");
		while (tbIt.hasNext()) {
			Element tbdata = tbIt.next();
			String tbName = tbdata.attributeValue("TABLE_NAME");
			tbList.add(tbName);
		}
		return tbList;
	}

	private static Element GetTableElement(Element dataRoot, String tbName) {

		@SuppressWarnings("unchecked")
		Iterator<Element> tbIt = dataRoot.elementIterator("TABLE");
		while (tbIt.hasNext()) {
			Element tbElement = tbIt.next();
			if (tbElement.attributeValue("TABLE_NAME").equalsIgnoreCase(tbName)) {
				return tbElement;
			}
		}
		return null;
	}

	private static Element GetTableStruct(Element structRoot, String tbName) {
		@SuppressWarnings("unchecked")
		Iterator<Element> tbSIt = structRoot.elementIterator("TABLE");
		while (tbSIt.hasNext()) {
			Element tbs = tbSIt.next();
			if (tbs.attributeValue("TABLE_NAME").equalsIgnoreCase(tbName)) {
				return tbs;
			}
		}
		return null;
	}

	private static List<String> GetSqlList(Element tbdata,
			List<String> columnNameList, List<Boolean> binaryFilterList)
			throws DocumentException, Exception {

		List<String> sqlList = new ArrayList<String>();

		StringBuffer sql = new StringBuffer();

		String tbName = tbdata.attributeValue("TABLE_NAME");

		String tableString = GetTableString(tbName);
		@SuppressWarnings("unchecked")
		Iterator<Element> fIt = tbdata.elementIterator("FIELD");

		while (fIt.hasNext()) {
			List<String> nameList = new ArrayList<String>();
			List<String> valueList = new ArrayList<String>();
			Element field = fIt.next();
			@SuppressWarnings("unchecked")
			Iterator<Element> cIt = field.elementIterator();
			while (cIt.hasNext()) {
				Element cell = cIt.next();

				if (cell.getText() != "") {
					nameList.add(cell.getName());
					valueList.add(cell.getText());
				}
			}
			String fieldString = GetFieldString(nameList, valueList,
					columnNameList, binaryFilterList);

			sql = new StringBuffer(tableString + fieldString + "\n");

			// System.out.println(sql);
			sqlList.add(sql.toString());
		}

		return sqlList;
	}

	private static List<String> GetBinaryColumns(List<String> columnNameList,
			List<Boolean> binaryFilterList) throws DocumentException, Exception {

		List<String> binaryColumnList = new ArrayList<String>();
		for (int i = 0; i < binaryFilterList.size(); i++) {
			if (binaryFilterList.get(i)) {
				binaryColumnList.add(columnNameList.get(i));
			}
		}
		return binaryColumnList;
	}

	private static String GetPreparedSql(String tbName,
			List<String> columnNameList) {
		String tableString = GetTableString(tbName);

		int begin = 0;
		StringBuffer fieldString = new StringBuffer();
		for (int i = begin; i < columnNameList.size(); i++) {
			if (i == begin) {
				fieldString.append(" " + "(" + columnNameList.get(i));
			} else {
				fieldString.append("," + columnNameList.get(i));
			}
		}
		fieldString.append(")" + " " + "VALUES");
		for (int i = begin; i < columnNameList.size(); i++) {

			if (i == begin) {
				fieldString.append(" " + "(" + "?");
			} else {
				fieldString.append("," + "?");
			}
		}
		fieldString.append(")" + "");
		return tableString + fieldString.toString();
	}

	public static void StoreData(DbConnection dbCon, String dataFile)
			throws DocumentException, Exception {

		Document dataDoc = MyDom4j.Read(dataFile);
		Element dataRoot = dataDoc.getRootElement();

		String structFile = dataRoot.attributeValue("STRUCT");
		Document structDoc = MyDom4j.Read(structFile);
		Element structRoot = structDoc.getRootElement();
		String dbType = structRoot.attributeValue("DBTYPE");

		Document dataTypdDoc = MyDom4j
				.Read("C:\\Users\\Administrator\\workspace\\Graduate\\xml\\DataType.xml");
		Element dataTypeElement = dataTypdDoc.getRootElement().element(dbType);

		List<String> tbLNameList = GetTableNameList(dataRoot);
		// System.out.println(tbLNameList);

		for (int tbIndex = 0; tbIndex < tbLNameList.size(); tbIndex++) {

			Element tbdata = GetTableElement(dataRoot, tbLNameList.get(tbIndex));

			Element tbStruct = GetTableStruct(structRoot,
					tbLNameList.get(tbIndex));

			List<String> columnNameList = GetColumnNameList(tbStruct);

			List<String> columnTypeList = GetColumnTypeList(tbStruct);

			List<String> rawTypeList = GetRawTypeList(columnTypeList,
					dataTypeElement);

			List<Boolean> binaryFilterList = BinaryFilter(columnTypeList,
					dataTypeElement);

			List<String> binaryColumnList = GetBinaryColumns(columnNameList,
					binaryFilterList);

			if (binaryColumnList.size() == 0) {
				List<String> sqllist = GetSqlList(tbdata, columnNameList,
						binaryFilterList);
				// System.out.println(sqllist);
				dbCon.ExcuteTransaction(sqllist);
			} else {
				String preSql = GetPreparedSql(tbLNameList.get(tbIndex),
						columnNameList);
				// System.out.println(preSql);

				PreparedStatement ps = dbCon.GetPreparedStatement(preSql);

				@SuppressWarnings("unchecked")
				Iterator<Element> fIt = tbdata.elementIterator("FIELD");

				while (fIt.hasNext()) {
					Element field = fIt.next();
					// System.out.println(field.getName());
					for (int columnIndex = 0; columnIndex < columnNameList
							.size(); columnIndex++) {
						Element cell = field.element(columnNameList
								.get(columnIndex));

						if (rawTypeList.get(columnIndex).equalsIgnoreCase(
								"boolean")) {
							ps.setBoolean(columnIndex + 1,
									Boolean.parseBoolean(cell.getText()));
						}

						if (rawTypeList.get(columnIndex).equalsIgnoreCase(
								"byte")) {
							ps.setShort(columnIndex + 1,
									Byte.parseByte(cell.getText()));
						}

						if (rawTypeList.get(columnIndex).equalsIgnoreCase(
								"short")) {
							ps.setShort(columnIndex + 1,
									Short.parseShort(cell.getText()));
						}
						if (rawTypeList.get(columnIndex)
								.equalsIgnoreCase("int")) {
							ps.setInt(columnIndex + 1,
									Integer.parseInt(cell.getText()));
							// System.out.println("int");
						}
						if (rawTypeList.get(columnIndex).equalsIgnoreCase(
								"long")) {
							ps.setLong(columnIndex + 1,
									Long.parseLong(cell.getText()));
						}
						if (rawTypeList.get(columnIndex).equalsIgnoreCase(
								"double")) {
							ps.setDouble(columnIndex + 1,
									Double.parseDouble(cell.getText()));
						}
						if (rawTypeList.get(columnIndex).equalsIgnoreCase(
								"float")) {
							ps.setFloat(columnIndex + 1,
									Float.parseFloat(cell.getText()));
						}
						if (rawTypeList.get(columnIndex).equalsIgnoreCase(
								"decimal")) {
							ps.setDouble(columnIndex + 1,
									Double.parseDouble(cell.getText()));
						}
						if (rawTypeList.get(columnIndex).equalsIgnoreCase(
								"String")) {
							ps.setString(columnIndex + 1, cell.getText());
						}
						if (rawTypeList.get(columnIndex).equalsIgnoreCase(
								"Base64Binary")) {
							// System.out.println("Base64Binary");
							byte[] buffer = Base64.DecodeBase64String(cell
									.getText());
							InputStream is = new ByteArrayInputStream(buffer);
							ps.setBinaryStream(columnIndex + 1, is);
						}
					}
					ps.addBatch();
				}
				ps.executeBatch();
			}
		}

	}

	public static void main(String[] args) throws DocumentException, Exception {
		// System.out.println(StoreData("XXYDATA_test.xml"));

	}
}

// private static List<Element> GetStruct(String fileName)
// throws DocumentException, Exception {
// Document doc = MyDom4j.Read(fileName);
// Element root = doc.getRootElement();
// List<Element> tableList = new ArrayList<Element>();
// @SuppressWarnings("unchecked")
// Iterator<Element> tbIt = root.elementIterator();
// while (tbIt.hasNext()) {
// Element tb = tbIt.next();
// tableList.add(tb);
// }
// return tableList;
// }
